package com.lyw.db.dao

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.RawQuery
import androidx.room.Update
import androidx.sqlite.db.SimpleSQLiteQuery
import androidx.sqlite.db.SupportSQLiteQuery
import com.lyw.db.entites.Poem

@Dao
interface PoemDao {

    @Query("UPDATE poem SET user_like = :like WHERE id=:id")
    fun updatePoemLike(id: Int,like:Int);

    // 分页查询
    @Query("SELECT * FROM poem LIMIT :limit OFFSET :offset")
    fun getPoemByRange(limit: Int, offset: Int): List<Poem>

    /**
     * 随机获取一个数据
     */
    @RawQuery
    fun getRandomPoem(query: SupportSQLiteQuery): List<Poem>

    // 你可以创建一个帮助函数来构建这个查询
    fun getRandomPoemHelper(limit:Int): List<Poem> {
        // 这里我们构建了一个原生 SQL 查询
        val sql = "SELECT * FROM poem ORDER BY RANDOM() LIMIT $limit"
        // 使用 Room 提供的 SimpleSQLiteQuery 来执行查询
        return getRandomPoem(SimpleSQLiteQuery(sql))
    }


    @Query("SELECT * FROM poem")
    fun getAllPoems(): List<Poem>

    @Query("SELECT * FROM poem WHERE id = :id")
    fun getPoemsById(id: Int): Poem?

    @Query("SELECT * FROM poem WHERE writer_id = :writerId")
    fun getPoemsByWriter(writerId: Int): List<Poem>

    //根据tag id，去poem_or_tag查出对应的
    @Query("SELECT poem.* FROM poem INNER JOIN poem_or_tag ON poem.id=poem_or_tag.poem_id " +
            "WHERE poem_or_tag.tag_id=:tagId")
    fun getPoemsByTagId(tagId: Int): List<Poem>


    //根据tag id，去poem_or_tag查出对应的
    @Query("SELECT poem.* FROM poem " +
            "INNER JOIN poem_or_tag ON poem.id = poem_or_tag.poem_id " +
            "INNER JOIN poem_tag ON poem_or_tag.tag_id = poem_tag.id " +
            "WHERE poem_tag.title = :title")
    fun getPoemsByTagTitle(title: String): List<Poem>

    @Insert
    fun insertAll(vararg poems: Poem)

    @Insert
    fun insertAll( poems: List<Poem>)
}